package com.diy.sigmund.diycommon.test.controller.excel.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.diy.sigmund.diycommon.entity.base.exception.DiyRuntimeException;
import com.diy.sigmund.diycommon.spring.CustomApplicationContext;
import com.diy.sigmund.diycommon.test.entity.FormDataDTO;
import com.diy.sigmund.diycommon.util.json.JacksonUtil;
import com.diy.sigmund.diycommon.util.seq.SeqUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.tomcat.util.codec.binary.Base64;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

/**
 * <br>https://mp.weixin.qq.com/s/Mt8_EE0NsH-cSUdJb5b8Xg
 * <br>https://blog.csdn.net/wuzhiwei549/article/details/105874226
 * <br>https://blog.csdn.net/qq_32258777/article/details/89031479
 * <br>https://blog.csdn.net/jianggujin/article/details/80200400
 *
 * @author ylm-sigmund
 * @since 2021/9/14 17:23
 */
@RestController
public class ExcelExportControllerOne {

    Logger log = LoggerFactory.getLogger(ExcelExportControllerOne.class);
    /**
     * 匹配固定字符串+一定位数数字 KNIGHT123456789012345
     */
    private static final Pattern PATTERN = Pattern.compile("KNIGHT\\d{15}");

    public static void main(String[] args) {
        for (int i = 0; i < 50000; i++) {
            System.out.print(i + 1);
            System.out.print(" ");
            System.out.println("KNIGHT" + SeqUtil.getRequestId().substring(0, 15));
        }
    }

    /**
     * <br>导入文件，逐行解析，分批写入，监控内存变化曲线.
     * <br>50000数据量，与同步解析，同步入库，区别不大，因为这里没有使用数据库持久化，数据停留的时间短。
     * <br>jps拿到id，使用jvisualvm监控堆变化
     * <br>项目启动初始是100MB
     * <br>第1次调用是到300MB，然后降到100MB
     * <br>第2次调用是到150MB，然后降到100MB
     * <br>第3次调用是到400MB，然后不降
     */
    @PostMapping("/testImportAndExport")
    public void testImportAndExport(FormDataDTO formDataDTO,
            @RequestPart("file") MultipartFile file) throws IOException {
        // org.apache.tomcat.util.codec.binary.Base64可以对字节数组加解密传输
        String s = Base64.encodeBase64String(file.getBytes());
        byte[] bytes = Base64.decodeBase64(s);
        // feign file传输，传输base64加密的字符串，再解密转解析为文件

        log.info("formDataDTO:{}", JacksonUtil.toJson(formDataDTO));
        String originalFilename = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();

        // 这里 需要指定写用哪个class去写
        // ExcelWriter excelWriter = EasyExcel.write(baos, ExportTestBO.class).build();
        ExcelWriter excelWriter = EasyExcel.write(baos)
                // 自动列宽(不太精确)
                // 这个目前不是很好用，比如有数字就会导致换行。而且长度也不是刚好和实际长度一致。 所以需要精确到刚好列宽的慎用。 当然也可以自定义实现
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .head(customHead())
                .build();
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();

        // 匿名内部类 不用额外写一个DemoDataListener
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(inputStream, ImportTestBO.class, new ReadListener<ImportTestBO>() {
            /**
             * 单次缓存的数据量
             */
            public static final int BATCH_COUNT = 1000;
            /**
             *临时存储
             */
            private List<ImportTestBO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            /** 临时存储去重集合 */
            private final Set<ImportTestBO> distinctSet = Sets.newHashSet();


            /**
             * 这里会一行行的返回头
             */
            @Override
            public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
                Integer approximateTotalRowNumber = context.readSheetHolder().getApproximateTotalRowNumber();
                log.info("总条数：{}", approximateTotalRowNumber);
                if (approximateTotalRowNumber > 50001) {
                    throw new DiyRuntimeException("行数超过限制");
                }
                log.info("解析到一条头数据:{}", JacksonUtil.toJson(headMap));
                context.readWorkbookHolder().setIgnoreEmptyRow(false);
                // 解析到一条头数据:{"0":{"type":"STRING","stringValue":"额度利率导入模板表","formula":false,"dataFormat":0,"dataFormatString":"General"}}
                // log.info("解析到一条头数据:{}", JacksonUtil.toJson(headMap));
            }

            @Override
            public void invoke(ImportTestBO data, AnalysisContext context) {
                cachedDataList.add(data);
                if (cachedDataList.size() >= BATCH_COUNT) {
                    saveData();
                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                saveData();
            }

            /**
             * 加上存储数据库
             */
            private void saveData() {
                // fix: 如果重复的数据不在同一个cachedDataList内,则会去重失败
                // 对数据进行去重,使用全局的对象distinctSet来维护
                cachedDataList.removeIf(e -> !distinctSet.add(e));
                checkDataFormat(cachedDataList);
                // List<ExportTestBO> exportTestBOs = BeanCopyUtil.copyList(cachedDataList, ExportTestBO.class);
                // // 写入excel
                // excelWriter.write(exportTestBOs, writeSheet);

                // 可直接写入原始数据
                excelWriter.write(cachedDataList, writeSheet);

                log.info("{}条数据，开始存储数据库！", cachedDataList.size());
                log.info("存储数据库成功！");
            }

            private void checkDataFormat(List<ImportTestBO> cachedDataList) {
                for (ImportTestBO importTestBO : cachedDataList) {
                    String data = importTestBO.getData();
                    if (StringUtils.isBlank(data)) {
                        continue;
                    }
                    if (!PATTERN.matcher(data).matches()) {
                        importTestBO.setDesc("格式有误");
                    }
                }
            }
        }).sheet().doRead();

        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
        log.info("excel解析、读写均完成");
        HttpServletResponse response = CustomApplicationContext.getHttpServletResponse();
        // 重置response
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        Map<String, String> map = MapUtils.newHashMap();
        map.put("code", "200");
        map.put("msg", "excel解析、读写均完成");
        response.getWriter().println(JacksonUtil.toJson(map));

        // InputStream is = new ByteArrayInputStream(baos.toByteArray());
        // List<ImportTestBO> list = EasyExcel.read(is)
        //         .head(ImportTestBO.class)
        //         .sheet("模板")
        //         .headRowNumber(1)
        //         .doReadSync();
        // // 数据可以正常解析
        // System.out.println(list.size());
    }

    private List<List<String>> customHead() {
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add("序号" + System.currentTimeMillis());
        List<String> head1 = new ArrayList<>();
        head1.add("数据" + System.currentTimeMillis());
        List<String> head2 = new ArrayList<>();
        head2.add("描述" + System.currentTimeMillis());
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }

    @PostMapping("/testImportAndExportSync")
    public void testImportAndExportSync(FormDataDTO formDataDTO,
            @RequestPart("file") MultipartFile file) throws IOException {
        log.info("formDataDTO:{}", JacksonUtil.toJson(formDataDTO));
        String originalFilename = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        InputStream is = new ByteArrayInputStream(baos.toByteArray());
        List<ImportTestBO> list = EasyExcel.read(inputStream)
                .head(ImportTestBO.class)
                .sheet()
                .headRowNumber(1)
                .doReadSync();
        for (ImportTestBO importTestBO : list) {
            String data = importTestBO.getData();
            if (StringUtils.isBlank(data)) {
                continue;
            }
            if (!PATTERN.matcher(data).matches()) {
                importTestBO.setDesc("格式有误");
            }
        }
        EasyExcel.write(baos, ExportTestBO.class).sheet().doWrite(list);

        log.info("excel解析、读写均完成");
        HttpServletResponse response = CustomApplicationContext.getHttpServletResponse();
        // 重置response
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        Map<String, String> map = MapUtils.newHashMap();
        map.put("code", "200");
        map.put("msg", "excel解析、读写均完成");
        response.getWriter().println(JacksonUtil.toJson(map));

    }

    @PostMapping("/export1")
    public void export1(@RequestParam MultipartFile file) throws IOException {
        List<RespCustomerDailyExport> list = getRespCustomerDailyExports(10);
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        //        ServletOutputStream os = response.getOutputStream();
        long exportStartTime = System.currentTimeMillis();
        log.info("报表导出Size: " + list.size() + "条。");

        HttpServletResponse response = CustomApplicationContext.getHttpServletResponse();
        String fileName = "exportDemo";
        // setHead(response, fileName);

        EasyExcel.write(os, RespCustomerDailyExport.class)
                .sheet("sheet1")
                .doWrite(list);

        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);

        // 导出和导入的对象定义是不一致的
        List<ReqCustomerDailyImport> reqCustomerDailyImports = EasyExcel.read(is)
                .head(ReqCustomerDailyImport.class)
                .sheet("sheet1")
                .headRowNumber(1)
                .doReadSync();
        // 数据可以正常解析
        System.out.println(reqCustomerDailyImports.size());

        // 文件落地，用来测试文件的格式和数据的完整性
        // @Cleanup:lombok关流注解
        FileOutputStream fileOutputStream = new FileOutputStream("F:\\tmp\\" + fileName + ".xlsx");
        try (BufferedInputStream bis = new BufferedInputStream(is);
                BufferedOutputStream bos = new BufferedOutputStream(fileOutputStream);) {
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            log.info("文件落地磁盘");
        } catch (Exception e) {
            log.error("", e);
        }

        // 文件上传到OSS
        // FileUploadUtil_Test.upLocalFileToOss(is, fileName);

        System.out.println(
                "报表导出结束时间:" + new Date() + ";导出耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");

        // ————————————————
        //         版权声明：本文为CSDN博主「雪孤城」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
        //         原文链接：https://blog.csdn.net/xueguchen/article/details/115102765
    }

    public void setHead(HttpServletResponse response, String fileName) {

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
            log.error("编码异常");
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

    }

    private List<RespCustomerDailyExport> getRespCustomerDailyExports(int count) {
        List<RespCustomerDailyExport> respCustomerDailyImports = Lists.newArrayList();
        for (int i = 0; i < count; i++) {
            RespCustomerDailyExport respCustomerDailyExport = new RespCustomerDailyExport();
            respCustomerDailyExport.setCustomerName("  ss  customerName小明-" + i);
            respCustomerDailyExport.setMisCode(i + "  s ");
            respCustomerDailyExport.setMonthlyQuota(new BigDecimal(String.valueOf(i)));
            respCustomerDailyExport.setAccountReceivableQuota(new BigDecimal(String.valueOf(i)));
            respCustomerDailyExport.setDailyInterestRate(new BigDecimal(String.valueOf(i)));
            respCustomerDailyImports.add(respCustomerDailyExport);
        }
        return respCustomerDailyImports;
    }
}
